INSERT INTO cat_advertisement
SELECT
newid() AS [Id],case len(convert(varchar(10),companyid))
    when 5 then '00000000-0000-0000-0000-0000000' + convert(varchar(10),companyid) 
    when 4 then '00000000-0000-0000-0000-00000000' + convert(varchar(10),companyid) 
    when 3 then '00000000-0000-0000-0000-000000000' + convert(varchar(10),companyid) 
    when 2 then '00000000-0000-0000-0000-0000000000' + convert(varchar(10),companyid) 
    when 1 then '00000000-0000-0000-0000-00000000000'+ convert(varchar(10),companyid) 
    end AS [CompanyId],1 AS [AreaCode],case len(convert(varchar(10),companyid))
    when 5 then '00000000-0000-0000-0000-0000000' + convert(varchar(10),companyid) 
    when 4 then '00000000-0000-0000-0000-00000000' + convert(varchar(10),companyid) 
    when 3 then '00000000-0000-0000-0000-000000000' + convert(varchar(10),companyid) 
    when 2 then '00000000-0000-0000-0000-0000000000' + convert(varchar(10),companyid) 
    when 1 then '00000000-0000-0000-0000-00000000000'+ convert(varchar(10),companyid) 
    end AS [MaterialId],Updateddate AS [StartDate],ExpiredDate AS [EndDate],VisitedCount AS [Clicks],0 AS [Status],Approved AS [IsPosted],PositionOrder AS [SortOrder],note AS [Notes],CreatedDate AS [DateCreated],updateddate AS [LastUpdated],'00000000-0000-0000-0000-000000000053' AS [ModifiedBy]FROM srv_lnk.nchr2008.dbo.ad
where  id in(SELECT max(id) FROM srv_lnk.nchr2008.dbo.ad group by companyid)
order by Expireddate desc
